package com.lianxi.exacl;

import com.alibaba.excel.EasyExcel;
import com.lianxi.TestME;
import com.lianxi.jdbc.DBHelper;
import com.lianxi.jdbc.DamengDBHelper;
import com.lianxi.pojo.SyxUser;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class ImportExcel {

    private static Logger logger = LoggerFactory.getLogger(ImportExcel.class);

    public static void main(String[] args) throws ParseException {
        List<TongYong> infoList = getInfoList();
        logger.info("查询结果数为：{}", infoList.size());
        String filename = "D:\\Desktop\\xyzx.xlsx";
        EasyExcel.write(filename, TongYong.class).sheet("【mi河】").doWrite(infoList);
    }


    /**
     * 查询
     */
    public static List<TongYong> getInfoList() throws ParseException {
        String sql = " select p.POST_TITLE ,p.POST_CONTENT \n" +
                "from xyzx.cmf_portal_post p \n" +
                "where p.POST_CONTENT like '%mi河%'\n" +
                "or p.POST_TITLE like '%mi河%';";
        List<TongYong> list = new ArrayList<>();
        Statement stmt = null;
        Connection con = null;
        HashMap<Integer, Integer> map = new HashMap<>();
        String url = null;
        try {
            con = DamengDBHelper.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            stmt = (Statement) con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            SimpleDateFormat formattime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            if (rs != null) {
                while (rs.next()) {
                    TongYong tongYong = new TongYong();
                    tongYong.setStr1(rs.getString(1));
//                    tongYong.setStr2(rs.getString(2));
                    list.add(tongYong);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DamengDBHelper.close(con);
        }
        return list;
    }
}
